package prac.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import org.json.JSONArray;
import org.json.JSONObject;

import com.amkor.commons.util.db.MultiDBConnection;
import db.Constant;
import util.Validate;

public class SeongSuCondoDAO {
	
/**
 * @description define global variable.
 */
		
	private static final String CONDO_LIST = "eamkor.CONDO_LIST";
	private static final String CONDO_APPLY = "eamkor.CONDO_APPLY";
	private static final String PPOMSTP = "PPSLIB.PPOMSTP";
	private static final String TESTID = "sencha.personal";
	
	Validate v = new Validate();
		
/**
 * @author YeonJong Jeong
 * @description create new condo item.
 * @param HashMap (String)[year, season, number, date, region, resort, night, square, price]
 * @return String	
 */
	
	public String add_condo_item(HashMap<String, String> hm) throws Exception {
		
		int i = 0;
		int upt_result = 0;		
		StringBuffer sb = new StringBuffer();
		JSONObject jObject = new JSONObject();
		Connection m_conn = null;
		PreparedStatement m_psmt = null;
		ResultSet m_rs = null;
		
		
		try{
			m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
			sb.append("SELECT COUNT(NUMBER) FROM "+CONDO_LIST+" WHERE YEAR=? AND SEASON=? AND NUMBER=? AND REGION=? AND RESORT=? AND SUBSTR(DATE,1,10)=? AND SQUARE=?");
			
			m_psmt = m_conn.prepareStatement(sb.toString());			
			m_psmt.setString(++i, v.nullchk(hm.get("condo_year")));
			m_psmt.setString(++i, v.nullchk(hm.get("condo_season")));
			m_psmt.setString(++i, v.nullchk(hm.get("condo_number")));
			m_psmt.setString(++i, v.nullchk(hm.get("condo_region")));
			m_psmt.setString(++i, v.nullchk(hm.get("condo_resort")));
			m_psmt.setString(++i, v.nullchk(hm.get("condo_date")));
			m_psmt.setString(++i, v.nullchk(hm.get("condo_square")));
			
			m_rs = m_psmt.executeQuery();
			if(m_rs.next() && m_rs != null){
				upt_result = m_rs.getInt(1);
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
		}
		
		
		if(upt_result == 0){
			
			try{			
				
				i=0; sb = new StringBuffer();
				m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
				sb.append("INSERT INTO "+CONDO_LIST+" SET YEAR=?, SEASON=?, NUMBER=?, REGION=?, RESORT=?, DATE=?, SQUARE=?, NIGHT=?, PRICE=?, CREATE_DATE=NOW()");
				m_psmt = m_conn.prepareStatement(sb.toString());
				
				m_psmt.setString(++i, v.nullchk(hm.get("condo_year")));
				m_psmt.setString(++i, v.nullchk(hm.get("condo_season")));
				m_psmt.setString(++i, v.nullchk(hm.get("condo_number")));
				m_psmt.setString(++i, v.nullchk(hm.get("condo_region")));
				m_psmt.setString(++i, v.nullchk(hm.get("condo_resort")));
				m_psmt.setString(++i, v.nullchk(hm.get("condo_date")));
				m_psmt.setString(++i, v.nullchk(hm.get("condo_square")));
				m_psmt.setString(++i, v.nullchk(hm.get("condo_night")));
				m_psmt.setString(++i, v.nullchk(hm.get("condo_price")));
							
				upt_result = m_psmt.executeUpdate();
				if(upt_result != 0){
					jObject.put("success", true);
					jObject.put("date", v.nullchk(hm.get("condo_date")));
					jObject.put("region", v.nullchk(hm.get("condo_region")));
					jObject.put("resort", v.nullchk(hm.get("condo_resort")));
				}
				
			}catch(Exception e){	
				jObject.put("success", false);
				jObject.put("errors", "SQL SQLException");
				System.out.println(e);
				// e.printStackTrace();
			}finally{
				MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
			}
			
		}else{
			jObject.put("success", false);
			jObject.put("message", "등록하실려는 콘도가 이미 등재되어 있습니다.");
		}
					
		return jObject.toString();
	}
	
/**
 * @author YeonJong Jeong
 * @description fetch all of condo lists.
 * @param HashMap (String)[start, limit]
 * @return String	
 */
		
	public String fetch_condo_list(HashMap<String, String> hm) throws Exception {
		
	// get start, end
		String start = hm.get("start");
		String limit = hm.get("limit");
		
		int total = 0;
		String jsonStr = "";
		StringBuffer sb = new StringBuffer();		
		JSONObject jObject = null;
		JSONObject jResult = new JSONObject();
		JSONArray jArray = null;
		Connection m_conn = null;
		PreparedStatement m_psmt = null;
		ResultSet m_rs = null;
		
		try{
			
			m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
			sb.append("SELECT YEAR, SEASON, NUMBER, REGION, RESORT, DATE, SQUARE, NIGHT, PRICE FROM "+CONDO_LIST+" WHERE YEAR=2013 AND SEASON='W' ORDER BY YEAR, SEASON, NUMBER LIMIT ?, ?");
		
		
			m_psmt = m_conn.prepareStatement(sb.toString());			
			m_psmt.setInt(1, Integer.parseInt(start));
			m_psmt.setInt(2, Integer.parseInt(limit));
						
			m_rs = m_psmt.executeQuery();
								
			jArray = new JSONArray();
			if(m_rs != null){
				while(m_rs.next()){
					jObject = new JSONObject();	

					jObject.put("year", v.nullchk(m_rs.getString(1)));
					jObject.put("season", v.nullchk(m_rs.getString(2)));
					jObject.put("number", v.nullchk(m_rs.getString(3)));
					jObject.put("region", v.nullchk(m_rs.getString(4)));
					jObject.put("resort", v.nullchk(m_rs.getString(5)));
					jObject.put("date", v.nullchk(m_rs.getString(6)).substring(0,10));
					jObject.put("square", v.nullchk(m_rs.getString(7)));
					jObject.put("night", v.nullchk(m_rs.getString(8)));
					jObject.put("price", v.nullchk(m_rs.getString(9)));
					
					jArray.put(jObject);					
					
				}
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
		}	
			
		try{				
			sb = new StringBuffer();
			m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());			 
			sb.append("SELECT COUNT(SEASON) FROM "+CONDO_LIST+ " WHERE YEAR=2013 AND SEASON='W' ");
			
			m_psmt = m_conn.prepareStatement(sb.toString());					
			
			m_rs = m_psmt.executeQuery();
			
			if(m_rs != null && m_rs.next()){					
				total = m_rs.getInt(1);											
				jResult.put("total", total);				
			}
						
			if(jArray.length() > 0){
				jResult.put("admin_store_root", jArray);				
				jResult.put("success", true);
				jsonStr = jResult.toString();
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
		}
		
		return jsonStr;
	}
	
/**
 * @author YeonJong Jeong.
 * @description delete selected condo item.
 * @param HashMap (String)[year, season, number]
 * @return String
 */
	
	public String delete_condo_item(HashMap<String, String> hm) throws Exception{
		
		int upt_update = 0;
		StringBuffer sb = new StringBuffer();
		JSONObject jObject = new JSONObject();
		Connection m_conn = null;
		PreparedStatement m_psmt = null;
		ResultSet m_rs = null;
		
	// get the passed key for selected items.
		String tmp_key = "";
		String [] record_array = null;
		if(hm.containsKey("key")){
			tmp_key = hm.get("key");
			record_array = tmp_key.split(":");
		}
						
		try{
			
			if(record_array != null && record_array.length > 0){
				
				m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
				sb.append("DELETE FROM "+CONDO_LIST+" WHERE YEAR=? AND SEASON=? AND NUMBER=?");
				m_psmt = m_conn.prepareStatement(sb.toString());
				
				for(int i=0; i<record_array.length; i++){
					String [] keys = record_array[i].split("@");
					
					if(keys != null && keys.length > 0){	// key[0] : year, keys[1] : season, keys[2] : number
						if(!"".equals(v.nullchk(keys[0])) && !"".equals(v.nullchk(keys[1])) && !"".equals(v.nullchk(keys[2]))){
							
							m_psmt.setString(1, keys[0]);
							m_psmt.setString(2, keys[1]);
							m_psmt.setString(3, keys[2]);
							
							upt_update = m_psmt.executeUpdate();
							
						}
					}
															
				}	// for
				
				if(upt_update != 0){
					jObject.put("success", true);					
				}else{
					jObject.put("success", false);
				}
								
			}	// if
																		
		}catch(Exception e){
			jObject.put("success", false);
			jObject.put("errors", "Fail Deletion !!");
			e.printStackTrace();
		}finally{
			MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
		}
		
		return jObject.toString();
	}
	
/**
 * @author YeonJong Jeong.
 * @description apply for selected condo item.
 * @param HashMap (String)[year, season, number]
 * @return String
 */	
	
	public String apply_condo_item(HashMap<String, String> hm) throws Exception{
		
		int i = 0;
		int upt_result = 0;		
		int max_sequence = 0;		
		StringBuffer sb = new StringBuffer();
		Connection m_conn = null;
		PreparedStatement m_psmt = null;
		ResultSet m_rs = null;
		JSONObject jObject = new JSONObject();
		
		try{
			m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
			sb.append("SELECT COUNT(EMPID) FROM "+CONDO_APPLY+" WHERE YEAR=? AND SEASON=? AND EMPID=?");
			
			m_psmt = m_conn.prepareStatement(sb.toString());
			m_psmt.setString(++i, v.nullchk(hm.get("year")));
			m_psmt.setString(++i, v.nullchk(hm.get("season")));
			m_psmt.setString(++i, v.nullchk(hm.get("empid")));
			
			m_rs = m_psmt.executeQuery();
			if(m_rs != null && m_rs.next()){
				upt_result = m_rs.getInt(1);
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
		}
		
		if(upt_result == 0){
			
			try{
				i = 0;
				sb = new StringBuffer();
				
			// get the sequence of condo per year, season, condo number.
				max_sequence = fetch_apply_sequence_condo(hm.get("year"), hm.get("season"), hm.get("number"));
													
				m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
				sb.append("INSERT INTO "+CONDO_APPLY+" SET YEAR=?, SEASON=?, NUMBER=?, EMPID=?, SEQUENCE=?, TEL=?, CREATE_DATE=NOW()");
				
				m_psmt = m_conn.prepareStatement(sb.toString());
				m_psmt.setString(++i, v.nullchk(hm.get("year")));
				m_psmt.setString(++i, v.nullchk(hm.get("season")));
				m_psmt.setString(++i, v.nullchk(hm.get("number")));
				m_psmt.setString(++i, v.nullchk(hm.get("empid")));
				m_psmt.setInt(++i, max_sequence);
				m_psmt.setString(++i, v.nullchk(hm.get("condo_user_tel")));
				
				upt_result = m_psmt.executeUpdate();
				
				if(upt_result != 0){
					jObject.put("success", true);
					jObject.put("date", v.nullchk(hm.get("condo_user_date")));
					jObject.put("region", v.nullchk(hm.get("condo_user_region")));
					jObject.put("resort", v.nullchk(hm.get("condo_user_resort")));
					jObject.put("tel", v.convert_telephone(v.nullchk(hm.get("condo_user_tel"))));
				}
																						
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
			}
			
		}else{
			jObject.put("success", false);
			jObject.put("message", "한개 이상 콘도를 예약하실 수 없습니다.");
		}
				
		return jObject.toString();	
	}
	
/**
 * @author YeonJong Jeong.
 * @description fetch the max value of condo per each year, season, number.
 * @param year(연도), season(시즌), number(콘도번호)
 * @return int
 */
	
	private int fetch_apply_sequence_condo(String year, String season, String number) throws Exception {
		
		int max_sequence = 0;
		StringBuffer sb = new StringBuffer();
		Connection m_conn = null;
		PreparedStatement m_psmt = null;
		ResultSet m_rs = null;
		
		try{
			
			m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
			sb.append("SELECT MAX(SEQUENCE) FROM "+CONDO_APPLY+" WHERE YEAR=? AND SEASON=? AND NUMBER=?");
			
			m_psmt = m_conn.prepareStatement(sb.toString());
			m_psmt.setString(1, v.nullchk(year));
			m_psmt.setString(2, v.nullchk(season));
			m_psmt.setString(3, v.nullchk(number));
			
			m_rs = m_psmt.executeQuery();
			if(m_rs != null && m_rs.next()){
				max_sequence = m_rs.getInt(1);
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
		}
		
		return max_sequence+1;
	}	// fetch_apply_sequence_condonimium
	
/**
 * @author YeonJong Jeong.
 * @description fetch individual applied condo per each year, season, empid
 * @param HashMap (String)[year(연도), season(시즌), empid(사번)]
 * @return String
 */	
	
	public String fetch_apply_condo_list(HashMap<String, String> hm) throws Exception{
				
		String jsonStr = null;
		StringBuffer sb = new StringBuffer();
		JSONObject jObject = new JSONObject();		
		Connection m_conn = null;
		PreparedStatement m_psmt = null;
		ResultSet m_rs = null;
		
		try{
			m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
			sb.append("SELECT YEAR, SEASON, NUMBER, EMPID, TEL FROM "+CONDO_APPLY+ " WHERE YEAR=? AND SEASON=? AND EMPID=?");
			
			m_psmt = m_conn.prepareStatement(sb.toString());
			m_psmt.setString(1, v.nullchk(hm.get("year").toString()));
			m_psmt.setString(2, v.nullchk(hm.get("season").toString()));
			m_psmt.setString(3, v.nullchk(hm.get("empid").toString()));
			
			m_rs = m_psmt.executeQuery();
			if(m_rs != null && m_rs.next()){
				jObject.put("year", v.nullchk(m_rs.getString(1)));
				jObject.put("season", v.nullchk(m_rs.getString(2)));
				jObject.put("number", v.nullchk(m_rs.getString(3)));
				jObject.put("empid", v.nullchk(m_rs.getString(4)));
				jObject.put("tel", v.nullchk(m_rs.getString(5)));
				
			// fetch the condo information.
				jObject = (JSONObject)fetch_condo_information(hm.get("year").toString(), hm.get("season").toString(), jObject);
				jObject.put("success", true);
				
			// return	
				jsonStr = jObject.toString();
			}
								
		}catch(Exception e){
			e.printStackTrace(); 
		}finally{
			MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
		}
												
		return jsonStr;
		
	}	// fetch_apply_condonimium_list
	public String test_list() throws Exception{
		
		String jsonStr = null;
		StringBuffer sb = new StringBuffer();
		//JSONObject jsonObj = new JSONObject();		
		Connection m_conn = null;
		PreparedStatement m_psmt = null;
		ResultSet m_rs = null;
		JSONArray jsonArray = new JSONArray();
		
		try{
			m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
			sb.append("SELECT firstName, lastName, company, email, dob, age , coworker FROM "+TESTID+ " ");
			
			m_psmt = m_conn.prepareStatement(sb.toString());
			m_rs = m_psmt.executeQuery();
			if(m_rs != null){
				while(m_rs.next()){		
					jsonArray.put(getTestRecord(m_rs));
						
				}
			}
			JSONObject jsonObj = new JSONObject();
			if(jsonArray.length() > 0){
				jsonObj.put("test_store_root", jsonArray);
				jsonObj.put("success", true);
				jsonStr = jsonObj.toString();
			}
			
		}catch(Exception e){
			e.printStackTrace(); 
		}finally{
			MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
		}
												
		return jsonStr;
		
	}	
	private JSONObject getTestRecord(ResultSet m_rs) throws Exception{

 		JSONObject jsonObj = new JSONObject();

 		try{
 			
 			jsonObj.put("firstName", v.nullchk(m_rs.getString(1)));
 			jsonObj.put("lastName", v.nullchk(m_rs.getString(2)));
 			jsonObj.put("company", v.nullchk(m_rs.getString(3)));
 			jsonObj.put("email", v.nullchk(m_rs.getString(4)));
 			jsonObj.put("dob", v.nullchk(m_rs.getString(5)));
 			jsonObj.put("age", m_rs.getInt(6));
 			jsonObj.put("coworker", v.nullchk(m_rs.getString(7)));
			
 			
 		}catch(Exception e){
 			e.printStackTrace();
 		}
 		
 		return jsonObj;
 	}
	
	public String test_Insert(HashMap<String, String> hm) throws Exception{
		
		int i = 0;
		int upt_result = 0;		
		StringBuffer sb = new StringBuffer();
		Connection m_conn = null;
		PreparedStatement m_psmt = null;
		ResultSet m_rs = null;
		JSONObject jObject = new JSONObject();
		
		try{
				i = 0;
				sb = new StringBuffer();
				m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
				sb.append("INSERT INTO "+TESTID+" SET firstName=?, lastName=?, company=?, email=?, dob=?, age=?, coworker=?, crt_dt=NOW()");
				
				m_psmt = m_conn.prepareStatement(sb.toString());
				m_psmt.setString(++i , v.nullchk(hm.get("firstName")));
				m_psmt.setString(++i, v.nullchk(hm.get("lastName")));
				m_psmt.setString(++i, v.nullchk(hm.get("company")));
				m_psmt.setString(++i, v.nullchk(hm.get("email")));
				m_psmt.setString(++i, v.nullchk(hm.get("dob").substring(6,10)+"-"+hm.get("dob").substring(0,2)+"-"+hm.get("dob").substring(3,5)));
				m_psmt.setInt(++i, Integer.parseInt(hm.get("age")));
				m_psmt.setString(++i, v.nullchk(hm.get("coworker")));
				
				upt_result = m_psmt.executeUpdate();
				if(upt_result !=0){
					
					jObject.put("success", true);
					
				}
				
																						
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
			}
		
				
		return jObject.toString();	
	}
	/**
	 * @author YeonJong Jeong.
	 * @description fetch the condo information
	 * @param (String) [year(연도), season(시즌)], JSONObject
	 * @return JSONObject
	 */	
	
	private JSONObject fetch_condo_information(String year, String season, JSONObject jObject) throws Exception {
		
		StringBuffer sb = new StringBuffer();
		Connection m_conn = null;
		PreparedStatement m_psmt = null;
		ResultSet m_rs = null;
		
		try{
			
			m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
			sb.append("SELECT REGION, RESORT, DATE, SQUARE, NIGHT, PRICE FROM "+CONDO_LIST+ " WHERE YEAR=? AND SEASON=? AND NUMBER=?");
			
			m_psmt = m_conn.prepareStatement(sb.toString());
			m_psmt.setString(1, v.nullchk(year));
			m_psmt.setString(2, v.nullchk(season));
			m_psmt.setString(3, v.nullchk(jObject.get("number").toString()));			
			m_rs = m_psmt.executeQuery();
			
			if(m_rs.next() && m_rs != null){
				jObject.put("region", v.nullchk(m_rs.getString(1)));
				jObject.put("resort", v.nullchk(m_rs.getString(2)));
				jObject.put("date", v.nullchk(m_rs.getString(3).substring(0, 10)));
				jObject.put("square", v.nullchk(m_rs.getString(4)));
				jObject.put("night", v.nullchk(m_rs.getString(5)));
				jObject.put("price", v.nullchk(m_rs.getString(6)));
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
		}
		
		return jObject;
	}
	
	/**
	 * 
	 * delete the applied condo item.
	 * 
	 * @param HashMap (String)[year, season, empid)
	 * @return String
	 * 
	 */	
	
	public String delete_apply_condo_item(HashMap<String, String> hm) throws Exception {
				
		int upt_result = 0;
		StringBuffer sb = new StringBuffer();
		JSONObject jObject = new JSONObject();
		Connection m_conn = null;
		PreparedStatement m_psmt = null;
		ResultSet m_rs = null;
		
		try{
			
			m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
			sb.append("DELETE FROM "+CONDO_APPLY+" WHERE YEAR=? AND SEASON=? AND NUMBER=? AND EMPID=?");
			m_psmt = m_conn.prepareStatement(sb.toString());
		
			int i = 0;
			m_psmt.setString(++i, v.nullchk(hm.get("year")));
			m_psmt.setString(++i, v.nullchk(hm.get("season")));
			m_psmt.setString(++i, v.nullchk(hm.get("number")));
			m_psmt.setString(++i, v.nullchk(hm.get("empid")));
			
			upt_result = m_psmt.executeUpdate();
			if(upt_result != 0){
				jObject.put("success", true);				
			}
						
		}catch(Exception e){
			System.out.println(e);
			jObject.put("success", false);
			jObject.put("message", "[콘도 지원자 리스트 삭제 Error] 관리자에게 문의하시기 바랍니다. (6552)");
		}finally{
			MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
		}
		
		return jObject.toString();
	}	// delete_apply_condonimium_item
	
/**
 * @author YeonJong Jeong.
 * @description excel condo apply list.
 * @param String [year, differ]
 * @return String
 */
	
	public String fetch_excel_condo_list (String year, String season) throws Exception {
		
		StringBuffer sb = new StringBuffer();
		Connection m_conn = null;
		PreparedStatement m_psmt = null;
		ResultSet m_rs = null;
		
		JSONObject jObject = null;		
		JSONObject jsonResult = new JSONObject();
		JSONArray jsonArray = new JSONArray();
		
		try{
			
			m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
			sb.append("SELECT NUMBER, SEQUENCE, TEL, EMPID FROM "+CONDO_APPLY+" WHERE YEAR=? AND SEASON=? ORDER BY NUMBER, SEQUENCE");
			m_psmt = m_conn.prepareStatement(sb.toString());
			
			m_psmt.setString(1, year);
			m_psmt.setString(2, season);
			
			m_rs = m_psmt.executeQuery();			
			if(m_rs != null){
				while(m_rs.next()){
					jObject = new JSONObject();
					
					jObject.put("year", year);
					jObject.put("season", season);
					jObject.put("number", v.nullchk(m_rs.getString(1)));
					jObject.put("sequence", v.nullchk(m_rs.getString(2)));
				// gets condo information.
					jObject = (JSONObject)fetch_condo_information(year, season, jObject);				
					jObject.put("tel", v.convert_telephone(v.nullchk(m_rs.getString(3))));		
					jObject.put("empid", v.nullchk(m_rs.getString(4)));
				// gets personal information.
					jObject = (JSONObject)fetch_personal_info(jObject);
					
					jsonArray.put(jObject);
									
				}
			}
						
			if(jsonArray.length() > 0 && jsonArray != null){
				jsonResult.put("admin_apply_store_root", jsonArray);
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
		}
			
		return jsonResult.toString();
	}
	
	/**
	 * @author YeonJong Jeong.
	 * @description fetch personal information.
	 * @param String empid
	 * @return JSONObject
	 */
		
		public JSONObject fetch_personal_info(JSONObject jObject) throws Exception {
			
			StringBuffer sb = new StringBuffer();
			Connection m_conn = null;
			PreparedStatement m_psmt = null;
			ResultSet m_rs = null;			
		//	DepartmentDAO departDAO = new DepartmentDAO();
									
			try{
				
				m_conn = MultiDBConnection.getConnection(Constant.getDB2());
				sb.append("SELECT MFGJWC, ZDDEPT, ZDSECT, MFNAME FROM "+PPOMSTP+" WHERE MFEMPN=?");
				m_psmt = m_conn.prepareStatement(sb.toString());
				
				m_psmt.setString(1, jObject.get("empid").toString());				
				
				m_rs = m_psmt.executeQuery();			
				if(m_rs != null && m_rs.next()){
				//	jObject.put("plant", departDAO.findDescPlant(v.nullchk(m_rs.getString(1))).trim());
					jObject.put("team",  v.nullchk(m_rs.getString(2)).trim());
					jObject.put("group", v.nullchk(m_rs.getString(3)).trim());
					jObject.put("username", v.nullchk(m_rs.getString(4)).trim());								
				}
				
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
			}
				
			return jObject;
		}	
	
}
